
IF EXISTS(select * from sysobjects where name='M_AccountStatementManual_OrderAssignedAmoun_V')
	DROP view M_AccountStatementManual_OrderAssignedAmoun_V
go
create view M_AccountStatementManual_OrderAssignedAmoun_V
as
select  ContractNo as ContractNoMain,SalesOrderNo,sum(ApplyAmount) as OAppliedAmount
		,count(0) allcreditcheck,sum(CreditCheck) creditchecked
from	M_AccountStatementManual
where	DeleteFlag=0
group by ContractNo,SalesOrderNo

go

ALTER proc [dbo].[GetDeliveryAllApprovalList]

	@Condiction varchar(max)

as
   declare @str varchar(max)

set @str='select	distinct TDA.DeliveryApplyNo, TDA.CoCode, TDA.ContractNoMain, TDA.ContractNoCust, TDA.TradingSoNo, 
		TDA.DeliveryApplyDate, TDA.TradingState, TDA.TradingStateValue, TDA.TradingDsce,
		''/''+TDA.DeliveryApplyNo+''/-DANTID-/''+TDA.ContractNoMain+''/''+TDA.TradingSoNo+''/'' strID,
		TDAL.DeliveryApplyAmount Amount,TDAL.TradingCurrency
		,TDA.UpdateAuthor,TDA.UpdateDate
		,SC.SalesName,CT.CustomerName, CT.CustomerNo,CT.NotDeliveryAmount
		,round(isnull(OrderValue,0),2) OrderValue,OAmount,isnull(OAppliedAmount,0) OAppliedAmount
		,isnull(CRE.CEName,'''') CEName
		,case when T.OAmountApproved=0 then 0 when len(T.OAmountApproved)=0 then 0 else 
		T.OAmountApproved-T.OAmount end as OAmountApproved
		,VA.allcreditcheck-VA.creditchecked as creditcheckflag
from	M_SO_Trading_DeliveryApply TDA
		left join M_SO_Trading_DeliveryApply_Line_V TDAL 
		on TDA.ContractNoMain=TDAL.ContractNoMain and TDA.DeliveryApplyNo=TDAL.DeliveryApplyNo
		left join M_SalesCatalog SC on SC.ContractNoMain=TDA.ContractNoMain
		left join GetCustomerName_NotDeliveryAmount CT on CT.ContractNoMain=TDA.ContractNoMain
		left join M_OrderHandling O on O.OrderNo=TDA.TradingSoNo
		left join M_SO_Trading_OrderShippedAmount_V T on T.ContractNoMain=TDA.ContractNoMain and T.TradingSoNo=TDA.TradingSoNo
		left join M_AccountStatementManual_OrderAssignedAmoun_V VA on VA.ContractNoMain=TDA.ContractNoMain and VA.SalesOrderNo=TDA.TradingSoNo
		left join M_ContrctReviewAndEntry CRE on CRE.ContractNoMain=TDA.ContractNoMain
where	TDA.DeleteFlag=0    '
+@Condiction+
' order by TDA.UpdateDate DESC, TDA.ContractNoMain DESC, TDA.DeliveryApplyNo desc '

exec (@str)

go

